﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using ADUSWebApp.Models;

namespace ADUSWebApp.DBConnect
{
    public class SqlHelper
    {
        //数据库连接字符串
        readonly string conStr = @"server=CV0024024N1\SQLEXPRESS;uid=sa;pwd=Password@123;database=YiWorld;Min Pool Size=10;";
        string strAdd = @"INSERT INTO [dbo].[User] ( UserName, [Password],[Email] )VALUES ( '{0}', '{1}','{2}' )";
        string strUserExist = @"SELECT * FROM dbo.[User] WHERE UserName='{0}' AND Password='{1}'";
        string strGetSingleUser = @"SELECT * FROM  dbo.[User] WHERE UserName='{0}'";
        string strEditUser = @"UPDATE dbo.[User] SET Password = '{0}', Email = '{1}' WHERE UserName='{2}'";

        public void OpenConnection()
        {
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
        }

        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        /// 
        public bool ExistUser(UserModel model)
        {
            strUserExist = string.Format(strUserExist, model.LoginName, model.LoginPassword);
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(strUserExist, con);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            con.Close();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 判断用户登录是否成功
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UserLogin(LoginModel model)
        {
            strUserExist = string.Format(strUserExist, model.UserName, model.UserPwd);
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(strUserExist, con);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            con.Close();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns></returns>
        /// 
        public DataSet GetAllUsers()
        {
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            string getalluser = "SELECT * FROM [USER]";
            SqlCommand cmd = new SqlCommand(getalluser,con);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            con.Close();
            return ds;
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddUser(UserModel usermodel)
        {
            strAdd = string.Format(strAdd,usermodel.LoginName,usermodel.LoginPassword,usermodel.Email);
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(strAdd,con);
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if(i > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 查询单个用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataSet GetSingleUser(string userName)
        {
            strGetSingleUser = string.Format(strGetSingleUser, userName);
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(strGetSingleUser, con);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            con.Close();
            return ds;
        }

        /// <summary>
        /// 查询单个用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool EditUser(string userName, string userPassword, string userEmail)
        {
            strEditUser = string.Format(strEditUser, userPassword, userEmail, userName);
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(strEditUser, con);
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i > 0)
            {
                return true;
            }
            else 
            {
                return false;
            }
        }
    }
}